package com.ncloud.act.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.alibaba.fastjson.JSONObject;
import com.ncloud.act.entity.ChnlBill;

@Mapper
public interface ChnlBillMapper{

	@Select("SELECT total_id, chnl_id, chnl_name, cycle_id, fee, balance, bill_tag,date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id, "
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_bill "
			+ "where chnl_id = #{chnl_id} and balance>0 order by cycle_id ")
	@Results({
		@Result(column="total_id",property="total_id"),@Result(column="chnl_id",property="chnl_id"),
		@Result(column="chnl_name",property="chnl_name"),@Result(column="cycle_id",property="cycle_id"),
		@Result(column="fee",property="fee"),
		@Result(column="balance",property="balance"),@Result(column="bill_tag",property="bill_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlBill> getChnlBillByChnlID(Long chnl_id);
	@Select("SELECT total_id, chnl_id, chnl_name, cycle_id, fee, balance, bill_tag,date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id, "
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_bill "
			+ "where total_id = #{total_id} ")
	@Results({
		@Result(column="total_id",property="total_id"),@Result(column="chnl_id",property="chnl_id"),
		@Result(column="chnl_name",property="chnl_name"),@Result(column="cycle_id",property="cycle_id"),
		@Result(column="fee",property="fee"),
		@Result(column="balance",property="balance"),@Result(column="bill_tag",property="bill_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlBill> getChnlBillByTotalID(Long total_id);
	@Select("SELECT total_id, chnl_id, chnl_name, cycle_id, fee, balance, "
			+ "(select ifnull(sum(ifnull(adjust_fee,0)),0) from sys_chnl_adjustbill t where t.chnl_id=t1.chnl_id and t.bill_cycle_id<t1.cycle_id and t.pay_tag=1) as adjust_fee,"
			+ "(select ifnull(sum(ifnull(adjust_fee,0)),0) from sys_chnl_adjustbill t where t.total_id=t1.total_id) as current_adjust_fee,"
			+ "bill_tag, "
			+ "date_format(create_time, '%Y-%m-%d' ) as create_time, "
			+ "create_user_id,"
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark "
			+ "FROM sys_chnl_bill t1 "
			+ "where t1.create_time >=#{1} and  t1.create_time <=#{2} "
//			+ "and t1.chnl_id in (SELECT chnl_id FROM  sys_chnl where chnl_id = #{0} "
//			+ "union all "
//			+ "SELECT chnl_id FROM  sys_chnl where parent_id = #{0}) "
			+ "and FIND_IN_SET(t1.chnl_id,f_getChnlList(#{0}))")
	@Results({
		@Result(javaType=String.class,column="total_id",property="total_id"),@Result(javaType=String.class,column="chnl_id",property="chnl_id"),
		@Result(column="chnl_name",property="chnl_name"),@Result(column="cycle_id",property="cycle_id"),
		@Result(column="fee",property="fee"),
		@Result(column="balance",property="balance"),@Result(column="bill_tag",property="bill_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<JSONObject> getChnlBillInfo(Integer user_id,String start_time,String end_time);
	
	@Insert(value = "INSERT INTO sys_chnl_bill (total_id, chnl_id, chnl_name, cycle_id, fee, balance, bill_tag, create_time, create_user_id, update_time, update_user_id, remark)  "
									+ "values(#{total_id},#{chnl_id},#{chnl_name},#{cycle_id},#{fee},#{balance},#{bill_tag},#{create_time},#{create_user_id},#{update_time},#{update_user_id},#{remark})")
    public int insertChnlBill(ChnlBill chnlBill);
	
	/*@Insert(value = "INSERT INTO sys_chnl_bill (bill_id, chnl_id, user_id, order_id, cust_id, discnt_inst_id,cycle_id,fee, balance, bill_tag, create_time, create_user_id, update_time, update_user_id, remark)  "
			+ " SELECT bill_id, chnl_id, user_id, order_id, cust_id, discnt_inst_id,cycle_id,fee, balance, bill_tag, create_time, create_user_id, update_time, update_user_id, remark FROM sys_chnl_realbill where cycle_id = #{cycle_id}")
	public int insertChnlBillFromReal(Integer cycle_id);*/
	
	@Update(value = "UPDATE sys_chnl_bill SET balance = #{balance}, bill_tag = #{bill_tag}, update_time = #{update_time}, update_user_id = #{update_user_id} WHERE total_id = #{total_id} ")
    public int updateChnlBill(ChnlBill chnlBill);
}
